Query execution in SQL (Structured Query Language) is an integral part of system performance in terms of efficiency, time, accuracy, and cost. The way in which a query is executed can avoid or create problems regarding the quantity of space that needs to be sacrificed to execute the query and the amount of time required to retrieve the query result.
In an RDBMS (Relational Database Management System), users issue queries against the data that is stored in software constructs. When a user issues a query, the system compiles the query by producing a physical plan that can be executed against the database. For any such query, there are a variety of different physical plans that can be appropriately used. However, each plan is different—some plans will execute the query with a low cost, while other plans will execute the query with a high cost. The step of optimizing a query execution process by costing various plans is inherently expensive.
Therefore, for a class of queries that are similar to one another, one could use the same physical plan to execute those queries and costing could be performed only once for the whole class of queries. In SQL, these queries are identified as parameterized queries, where a user can submit a query with constants missing—those constants are not provided until runtime. In this situation, just one plan will be generated and stored in the plan cache for every query that belongs in that class of queries, effectively avoiding the cost of optimizing more than once for these queries. However, depending on the constant selected, there can be a dramatically wide range of plans corresponding to different costs.
One approach in plan selection is to optimize the first query submission with constants in place and use the selected plan for all subsequent queries in that cost. Another approach in plan selection is to provide the optimizer with a hint when submitting the parameterized query so that optimization will use the suggested constant to generate a plan.
While such approaches ensure that an optimal plan is selected for at least one constant, different plans have different levels of optimality for different constants. Selecting a single plan will almost always be suboptimal for at least some other parameter setting that does not correspond to the initial optimization approach. Thus, a plan that may perform well for some parameter values may perform poorly for other parameter values. In addition, there are parameterized queries for which no plan achieves close to optimal performance. Current database systems do not have a reasonable automatic response to such situations.